package com.xiang.dao.impl;

import com.xiang.DBTest;
import com.xiang.dao.UsersDao;
import com.xiang.pojo.OrderQueryVo;
import com.xiang.pojo.User;
import com.xiang.pojo.Users;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: xiang
 * Date: 2021/9/14 11:14
 */
@Repository
public class UserDaoImpl implements UsersDao  {
    Connection connection = DBTest.getConnection();
    private PreparedStatement ps;


    public boolean getLogin(Users users)  {

        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement("select  * from  users where  account=? and password=? ");
            statement.setString(1, users.getAccount());
            statement.setString(2, users.getPassword());
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

//        connection.close();
        return false;
    }
//(`account`, `password`, `username`, `sex`, `age`, `birthday`)

    public List<Users> getAll() {
        PreparedStatement statement = null;
        ArrayList<Users> list = new ArrayList<>();
        try {
            statement = connection.prepareStatement("select  * from  users");
            ResultSet resultSet = statement.executeQuery();

            while (resultSet.next()) {
                Users users = new Users();
                int anInt = resultSet.getInt(1);
                users.setId(anInt);
                String account = resultSet.getString("account");
                users.setAccount(account);
                String password = resultSet.getString("password");
                users.setPassword(password);
                String username = resultSet.getString("username");
                users.setUsername(username);
                String sex = resultSet.getString("sex");
                users.setSex(sex);
                int age = resultSet.getInt("age");
                users.setAge(age);
                Date birthday = resultSet.getDate("birthday");
                users.setBirthday(birthday);
                list.add(users);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

//        connection.close();
        return list;
    }


    public Users getId(int id)  {
        PreparedStatement statement = null;
        Users users = new Users();
        try {
            statement = connection.prepareStatement("select  * from  users where  id = ?");
            statement.setInt(1, id);
            ResultSet resultSet = statement.executeQuery();

            while (resultSet.next()) {

                int anInt = resultSet.getInt(1);
                users.setId(anInt);
                String account = resultSet.getString("account");
                users.setAccount(account);
                String password = resultSet.getString("password");
                users.setPassword(password);
                String username = resultSet.getString("username");
                users.setUsername(username);
                String sex = resultSet.getString("sex");
                users.setSex(sex);
                int age = resultSet.getInt("age");
                users.setAge(age);
                Date birthday = resultSet.getDate("birthday");
                users.setBirthday(birthday);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

//        connection.close();
        return users;
    }


    public boolean save(Users users) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement("update  " +
                    "users set account=?, password=?, username=?, sex=?, age=?, birthday=? where id =?");
            statement.setString(1, users.getAccount());
            statement.setString(2, users.getPassword());
            statement.setString(3, users.getUsername());
            statement.setString(4, users.getSex());
            statement.setInt(5, users.getAge());
            statement.setDate(6, users.getBirthday());
            statement.setInt(7, users.getId());
            int update = statement.executeUpdate();

            System.out.println("statement======>" + statement);
            if (update > 0) {
                System.out.println("update--ok");
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

//        connection.close();
        return false;

    }

    public boolean delId(int id)  {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement("delete from users where id = ?");
            statement.setInt(1, id);
            int update = statement.executeUpdate();
            if (update > 0) {
                return true;
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }


    //           <%--        //(`account`, `password`, `username`, `sex`, `age`, `birthday`)--%>
    public boolean getAdd(Users users) {
        try {
            PreparedStatement statement = connection.prepareStatement("insert into users (account,password, username, sex, age, birthday) values (?,?,?,?,?,?)");
            statement.setString(1, users.getAccount());
            statement.setString(2, users.getPassword());
            statement.setString(3, users.getUsername());
            statement.setString(4, users.getSex());
            statement.setInt(5,users.getAge());
            statement.setDate(6,users.getBirthday());
            int update = statement.executeUpdate();
            if (update>0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return false;
    }

    /**
    //    查询数据列表
    public List<Users> findOrderByVo(OrderQueryVo vo){
        connection = DBTest.getConnection();
        List<Users> orderlist = new ArrayList<Users>();

        try {
            PreparedStatement statement  = connection.prepareStatement("select * from  users limit ?,?");
            statement.setInt(1,vo.getStart());
            statement.setInt(2,vo.getSize());
            ResultSet resultSet = statement.executeQuery();


            while (resultSet.next()){
                Users users = new Users();
//                order.setOid(resultSet.getInt("oid"));
//                order.setOname(resultSet.getString("oname"));
//                order.setPrice(resultSet.getDouble("price"));
//                order.setDate(resultSet.getDate("date"));
                int anInt = resultSet.getInt(1);
                users.setId(anInt);
                String account = resultSet.getString("account");
                users.setAccount(account);
                String password = resultSet.getString("password");
                users.setPassword(password);
                String username = resultSet.getString("username");
                users.setUsername(username);
                String sex = resultSet.getString("sex");
                users.setSex(sex);
                int age = resultSet.getInt("age");
                users.setAge(age);
                Date birthday = resultSet.getDate("birthday");
                users.setBirthday(birthday);

                orderlist.add(users);
            }

            return orderlist;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
//            if (resultSet != null){
//                try {
//                    resultSet.close();
//                } catch (SQLException e) {
//                    e.printStackTrace();
//                }
//            }

//            if (statement != null){
//                try {
//                    statement.close();
//                } catch (SQLException e) {
//                    e.printStackTrace();
//                }
//            }

            if (connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }

    }

     */


    public List<Users> findOrderByVo(OrderQueryVo vo) {

//        cn = JdbcTest.getConnection();
        Connection cn = DBTest.getConnection();
        List<Users> list = new ArrayList<Users>();
        try {
            ps = cn.prepareStatement("select  * from users limit ?,?");
            ps.setInt(1, vo.getStart());
            ps.setInt(2, vo.getSize());
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
//                (`account`, `password`, `username`, `sex`, `age`, `birthday`)
                Users users = new Users();
//                users.setId(resultSet.getInt("id"));
//                users.setAccount(resultSet.getString("account"));
//                users.setUsername(resultSet.getString("username"));
//                users.setPassword(resultSet.getString("password"));
//                users.setSex(resultSet.getString("sex"));
//                users.setAge(resultSet.getInt("age"));
//                users.setBirthday(resultSet.getDate("birthday"));
                int anInt = resultSet.getInt(1);
                users.setId(anInt);
                String account = resultSet.getString("account");
                users.setAccount(account);
                String password = resultSet.getString("password");
                users.setPassword(password);
                String username = resultSet.getString("username");
                users.setUsername(username);
                String sex = resultSet.getString("sex");
                users.setSex(sex);
                int age = resultSet.getInt("age");
                users.setAge(age);
                Date birthday = resultSet.getDate("birthday");
                users.setBirthday(birthday);
                list.add(users);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                cn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;

    }

    //  查询数据总数
    public Integer findOrderByVoCount(OrderQueryVo vo){
        connection = DBTest.getConnection();

        try {

            PreparedStatement statement  = connection.prepareStatement("select count(*) as count from users ");
//            preparedStatement = connection.prepareStatement("select count(*) as count from order2");
            ResultSet resultSet = statement.executeQuery();
            int count = 0;
            while (resultSet.next()){
                count = resultSet.getInt("count");

            }
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return null;
    }
}
